﻿-- =========================================================================================================
-- Author:		MICHAEL RITACCO
-- Copyright:	2010 MEKHANO GROUP LLC
-- Version:		08/21/2010 1.0
-- =========================================================================================================
CREATE PROCEDURE [flcrTM].[api_tm_service_dml_mrg]
	(@TVP flcr.SERVICE_UDT READONLY)

AS
BEGIN TRY

	MERGE	[flcrTm].[SERVICE] A
	USING	[flcr].[SERVICE] B
	   ON	A.[SERVICE_ID] = B.[SERVICE_ID]
	  AND	[flcrTm].GetTMPeriodKey(B.AUDIT_MOD_DTTM) = A.[TIME_KEY]
	 WHEN NOT MATCHED BY TARGET THEN
			INSERT VALUES
			([flcrTm].GetTMPeriodKey(B.AUDIT_MOD_DTTM)
			   ,B.[SERVICE_ID]
			   ,B.[AUDIT_USER]
			   ,B.[AUDIT_DTTM]
			   ,B.[AUDIT_DTTM_TZ]
			   ,B.[AUDIT_MOD_USER]
			   ,B.[AUDIT_MOD_DTTM]
			   ,B.[AUDIT_MOD_DTTM_TZ]
			   ,B.[RLS_MASK]
			   ,B.[RLS_OWNER]
			   ,B.[BUILTIN]
			   ,B.[WRKFL_ITEM_ID]
			   ,B.[WRKFL_STATUS_ID]
			   ,B.[ACTIVE_FLG]
			   ,B.[SORT_ORDER]
			   ,B.[SERVICE_NAME]
			   ,B.[SERVICE_SNAME]
			   ,B.[SERVICE_DESC]
			   ,B.[SERVICE_TYPE]
			   ,B.[PARENT_ID]
			   ,B.[DOMAIN_ID]
			   ,B.[ORG_ID]
			   ,B.[RESOURCE_ID]
			   ,B.[SUPPLIER_ID]
			   ,B.[SLA_ID])
	 WHEN MATCHED AND (B.SERVICE_ID IN (SELECT [SERVICE_ID] FROM @TVP)) THEN
			UPDATE SET
			A.[AUDIT_MOD_USER] = B.[AUDIT_MOD_USER]
			,A.[AUDIT_MOD_DTTM] = B.[AUDIT_MOD_DTTM]
			,A.[AUDIT_MOD_DTTM_TZ] = B.[AUDIT_MOD_DTTM_TZ]
			,A.[RLS_MASK] = B.[RLS_MASK]
			,A.[RLS_OWNER] = B.[RLS_OWNER]
			,A.[BUILTIN] = B.[BUILTIN]
			,A.[WRKFL_ITEM_ID] =B.[WRKFL_ITEM_ID]
			,A.[WRKFL_STATUS_ID] = B.[WRKFL_STATUS_ID]
			,A.[ACTIVE_FLG] = B.[ACTIVE_FLG]
			,A.[SORT_ORDER] = B.[SORT_ORDER]
			,A.[SERVICE_NAME] = B.[SERVICE_NAME]
			,A.[SERVICE_SNAME] = B.[SERVICE_SNAME]
			,A.[SERVICE_DESC] = B.[SERVICE_DESC]
			,A.[SERVICE_TYPE] = B.[SERVICE_TYPE]
			,A.[PARENT_ID] = B.[PARENT_ID]
			,A.[DOMAIN_ID] = B.[DOMAIN_ID]
			,A.[ORG_ID] = B.[ORG_ID]
			,A.[RESOURCE_ID] = B.[RESOURCE_ID]
			,A.[SUPPLIER_ID] = B.[SUPPLIER_ID]
			,A.[SLA_ID] = B.[SLA_ID];		
			
END TRY
BEGIN CATCH
	EXEC [flcr].[utl_getDBError];
END CATCH